package org.damazio.flight.checklists.data;

import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;

public class CheckListsTable {

  public static final String TABLE_NAME = "checklists";
  public static final String LIST_ID_COLUMN = "list_id";

  private static final String CREATE_TABLE_SQL =
      "CREATE TABLE IF NOT EXISTS checklists " +
          "(list_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
          " parent_id INTEGER NOT NULL REFERENCES checklists(list_id) ON DELETE CASCADE," +
          " ordered INTEGER NOT NULL," +
          " name VARCHAR(255) NOT NULL);";
  private static final String POPULATE_TABLE_SQL =
      "INSERT OR REPLACE INTO checklists " +
          "VALUES (0, 0, 0, 'Root');";
  public static final String QUERY_SQL =
      "SELECT " + LIST_ID_COLUMN + " as " + CheckListsSchema.ID_COLUMN + "," +
      "'" + CheckListsSchema.LIST_TYPE + "' as " + CheckListsSchema.TYPE_COLUMN + "," +
      CheckListsSchema.NAME_COLUMN + "," +
      CheckListsSchema.ORDERED_COLUMN + "," +
      CheckListsSchema.PARENT_ID_COLUMN +
      " FROM " + TABLE_NAME + 
      " WHERE " + CheckListsSchema.ID_COLUMN + " <> 0";

  private final SQLiteDatabase db;

  CheckListsTable(SQLiteDatabase db) {
    this.db = db;
  }

  public void create() {
    db.execSQL(CREATE_TABLE_SQL);
    db.execSQL(POPULATE_TABLE_SQL);
  }

  public long insert(String name, long parentId) {
    ContentValues values = new ContentValues();
    values.put(CheckListsSchema.PARENT_ID_COLUMN, parentId);
    values.put(CheckListsSchema.ORDERED_COLUMN, 1);  // TODO: real ordering
    values.put(CheckListsSchema.NAME_COLUMN, name);

    return db.insertOrThrow(TABLE_NAME, null, values);
  }
}
